package testExcel;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.*;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.*;
import entity.Dict;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ResourceUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

@Slf4j
public class EasyExcelTest {
	public static void main(String[] args) {
		new EasyExcelTest().fillObjectAndListInMemory();
	}


	/**
	 * 导出Excel，通过请求体下载到桌面
	 * @param	headInfo Excel头部信息
	 * @param	fillObjectList 填充的数据列表
	 * @param	fillObjectSheetNo 填充的数据的sheet编号
	 * @param	templateName 模板文件名称
	 * @param	exportFileName 导出文件名称
	 * @param	setIndexFlag 是否设置index
	 * @param	response 请求体
	 */
	private void exportExcel(JSONObject headInfo, List<?> fillObjectList, int fillObjectSheetNo, String templateName,
	                         String exportFileName, boolean setIndexFlag, HttpServletResponse response) {
		if (fillObjectList.isEmpty()) {
			log.info("####导出登记信息[{}]，需导出条数为0", exportFileName);
			return;
		}
		JSONArray fillList = JSONArray.parseArray(JSON.toJSONString(fillObjectList));
		if (setIndexFlag) {
			for (int i = 0; i < fillList.size(); i++) {
				fillList.getJSONObject(i).put("index", i + 1);
			}
		}
		// 获取模板存储路径
		File templatesFile;
		try {
			templatesFile = ResourceUtils.getFile("classpath:templates/ft/"+templateName);
		} catch (FileNotFoundException e) {
			log.error("####导出登记信息[{}]，导出模板文件不存在：{}", exportFileName, "templates/ft/"+templateName);
			return;
		}

		ExcelWriter excelWriter;
		try {
			// 导出文件保存路径
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			response.setCharacterEncoding("utf-8");
			excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templatesFile).build();
		} catch (IOException e) {
			log.error("####导出登记信息[{}]，文件输出流获取异常", exportFileName);
			return;
		}

		try {
			WriteSheet writeSheet = EasyExcel.writerSheet(fillObjectSheetNo).build();
			if (headInfo != null){
				excelWriter.fill(headInfo, writeSheet);
			}
			FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
			excelWriter.fill(fillList, fillConfig, writeSheet);
		} catch (Exception e) {
			log.error("####导出登记信息[{}]，出现异常：{}", exportFileName, e.getMessage(), e);
		} finally {
			if (excelWriter != null) {
				excelWriter.finish();
			}
		}
	}

	/**
	 * 填充对象+列表，因为列表之后还有一个字段，所以需要将{@link FillConfigBuilder#forceNewRow(Boolean)}设置为 TRUE 才行。
	 * <p>
	 * 这样会有一个副作用：所有数据会在内存中，即数据量大的时候特别耗内存。
	 * <p>
	 * 想要解决有两种方式：
	 *
	 * <ul>
	 * <li>list 之后没有数据了，{@link FillConfigBuilder#forceNewRow(Boolean)}设置为 FALSE</li>
	 * <li>list 写完之后，手动写后面的数据</li>
	 * </ul>
	 * /
	 */
	private void fillObjectAndListInMemory() {
		String fileName = "test.xls";
		String fileName2 = "test2";
		String templateFile = "C:\\Users\\c9346\\Desktop\\";

		File templatesFile = FileUtil.file(templateFile, "\\", fileName);
		// 导出文件保存路径
		File exportFile = new File(StrUtil.concat(true, "C:\\Users\\c9346\\Desktop\\", File.separator, fileName2, ".xlsx"));
		ExcelWriter excelWriter = EasyExcel
				.write(exportFile)
				// 获取模板路径
				.withTemplate(templatesFile)
				.build();
		try {
			final WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();

			Map<String, Object> map = new HashMap<>();
			map.put("date", DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(LocalDateTime.now()));
			map.put("total", System.currentTimeMillis());
			excelWriter.fill(map, writeSheet);

			FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

			List<Dict> dats = this.createDats();
			JSONArray fillList = JSONArray.parseArray(JSON.toJSONString(dats));
			excelWriter.fill(fillList, fillConfig, writeSheet);
		} finally {
			if (excelWriter != null) {
				excelWriter.finish();
			}
		}
	}


	private List<Dict> createDats() {
		List<Dict> list = new ArrayList<>();
		for (int i = 0; i < 10; i++) {
			Dict dict = new Dict();
			dict.setIndex(String.valueOf(i));
			dict.setCode("code_" + String.valueOf(i));
			dict.setName("name_" + String.valueOf(i));
			dict.setValue("value_" + String.valueOf(i));
			list.add(dict);
		}
		return list;
	}
}
